import requests
import sqlalchemy
import json
from lxml import etree
from sqlalchemy import create_engine
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
# 连接数据库
engine = create_engine('mysql+pymysql://root:root@localhost:3306/test1?charset=utf8') # 连接mysql
# 声明性基类
Base = declarative_base(engine)
from sqlalchemy import Column, Integer, String
from sqlalchemy import Sequence

class AirportInfo(Base):
    # 表名
    __tablename__ = 'airport_infos'
    # 字符编码，默认为：latin1
    __table_args__ = {"mysql_charset" : "utf8"}
    id = Column(Integer, primary_key=True)
    country = Column(String(255))
    city_chinese_name = Column(String(255))
    city_english_name = Column(String(255))
    airport_3_code = Column(String(255), nullable=True)
    airport_4_code = Column(String(255), nullable=True)
    airport_chinese_name = Column(String(255), nullable=True)
    airport_english_name = Column(String(255), nullable=True)
    
    def __repr__(self):
        return '<AirportInfo(city_chinese_name="%s", city_english_name="%s", airport_3_code="%s", airport_4_code="%s", airport_chinese_name="%s", airport_english_name="%s")>'\
        %(city_chinese_name, city_english_name, airport_3_code, airport_4_code, airport_chinese_name, airport_english_name)

# 读取机场信息
def read_json(path):
    f = open(path, 'r')
    info = json.loads(f.read())
    f.close()
    return info

# 创建表（跳过已创建的）
Base.metadata.create_all()

# 创建会话
# ORM对数据库的“句柄”是Session
Session = sessionmaker()
# 当你使用create_engine()，连接到Session使用configure()
Session.configure(bind=engine)
# 每当需要与数据库进行对话时，都要实例化一个 Session
session = Session()

def detail_parse(url, country, current_page=1, maxpage=0):
    response = requests.get(url)
    response.encoding = 'utf8'
    html = etree.HTML(response.text)
    columns = html.xpath('/html/body/div[2]/div/div[2]/div/div[1]/div/div[1]/table/tbody/tr')
    deal_columns(columns, country)

    if maxpage == 0:
        maxpage = html.xpath('/html/body/div[2]/div/div[2]/div/div[1]/div/div[2]/div/ul/li/a/@href')
        if maxpage:
            if 'page' in maxpage[-1]:
                maxpage = int(maxpage[-1].split('-')[-1])
        else:
            maxpage = 1

    current_page += 1
    if current_page <= maxpage:
        url = url.split('-')[0]+ '-' + url.split('-')[1] + '__page-' + str(current_page)
        print(url)
        detail_parse(url, country, current_page, maxpage)
    else:
        return None

def deal_columns(columns, country):
    for column in columns:
        city_name = column.xpath('./td[1]//text()')
        airport_name = column.xpath('./td[2]//text()')
        airport_3_code = column.xpath('./td[3]//text()')
        airport_4_code = column.xpath('./td[4]//text()')

        city_chinese_name = city_name[0].strip()
        city_english_name = city_name[1].strip()
        airport_chinese_name = airport_name[0].strip()
        airport_english_name = airport_name[1].strip()
        if airport_3_code:
            airport_3_code = airport_3_code[0]
        else:
            airport_3_code = ''
        if airport_4_code:
            airport_4_code = airport_4_code[0]
        else:
            airport_4_code = ''
        print(city_chinese_name, city_english_name, airport_chinese_name, airport_english_name, airport_3_code, airport_4_code)
        record = AirportInfo(country=country, city_chinese_name=city_chinese_name, city_english_name=city_english_name, airport_chinese_name=airport_chinese_name, 
                            airport_english_name=airport_english_name, airport_3_code=airport_3_code, airport_4_code=airport_4_code)
        session.add(record)
        session.commit()

def main():

    base_url = 'http://airport.anseo.cn'
    xpath = '//*[@id="regions-list-c"]'

    response = requests.get(base_url)
    response.encoding ='utf8'
    # print(response.text)
    html = etree.HTML(response.text)
    countrys = html.xpath('//*[@id="regions-list-c"]/div/ul/li')
    for country in countrys:
        temp_url = country.xpath('./a/@href')[0]
        country = country.xpath('./a/text()')[0]
        # print(temp_url)
        url = base_url + temp_url
        detail_parse(url, country)
        
    session.close()

if __name__ == '__main__':
    main()